package com.ly.datacenter.jdbc.util;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * function:
 * @author watson  email: watson.wu@hotmail.com
 * @date 2009-9-1
 */
public class DatabaseUtil {
	
	private static final String MYSQL_DRIVER = "com.mysql.jdbc.Driver";
	private static final String SQLServer_DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	private static final String ORACLE_DRIVER = "oracle.jdbc.driver.OracleDriver";

	static {
	}
	
	public static Connection createConnection(String url, String username, String password) {
		try {
			String driver = "";
			if(url.contains("mysql"))
				driver = MYSQL_DRIVER;
			else if(url.contains("sqlserver"))
				driver = SQLServer_DRIVER;
			else if(url.contains("oracle"))
				driver = ORACLE_DRIVER;
			Class.forName(driver, true, Thread.currentThread().getContextClassLoader());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			System.exit(-1);
		}
		try {
			return DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public static Statement getStatement(String url, String username, String password) {
		Connection connection = createConnection(url, username, password);
		try {
			return connection.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static DatabaseMetaData getDatabaseMetaData(String url, String driver, String username, String password){
		Connection connection = createConnection(url, username, password);
		try {
			return connection.getMetaData();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public static DatabaseMetaData getDatabaseMetaData(Connection connection) {
		try {
			return connection.getMetaData();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public static String[] getTableNamesOfDatabase(Connection connection) {
		return getTableNamesOfDatabase(connection, null,null,null,new String[]{"TABLE"});
	}
	
	public static String[] getTableNamesOfDatabase(Connection connection, String catalog, String schemaPattern,
			String tableNamePattern, String types[]) {
		DatabaseMetaData metaData = getDatabaseMetaData(connection);
		try {
			ResultSet resultSet = metaData.getTables(catalog, schemaPattern, tableNamePattern, types);
//			int i=0;
//			while(resultSet.next()) {
//				++i;
//			}
//			if(i>0){
//				String[] tableNames = new String[i];
//				resultSet.beforeFirst();
//				i=0;
			List<String> list = new ArrayList<String>();
				while(resultSet.next()) {
	//				String tableName = resultSet.getString(3);
	//			    
	//	            // Get the table's catalog and schema names (if any)
	//	            String tableCatalog = resultSet.getString(1);
	//	            String tableSchema = resultSet.getString(2);
//					tableNames[i++] = resultSet.getString(3);
					list.add(resultSet.getString(3));
				}
				String[] tableNames = new String[list.size()];
				for(int i=0;i<list.size();i++)
					tableNames[i] = list.get(i);
				return tableNames;
//			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public static ResultSet getTableInfoByTableName(Connection connection, String tableName) {
		try {
			DatabaseMetaData metaData = getDatabaseMetaData(connection);
			return metaData.getTables(null, null, tableName, new String[]{"TABLE"});
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public static List<String> getTablePrimaryKeyByTableName(Connection connection, String tableName) {
		try {
			DatabaseMetaData metaData = getDatabaseMetaData(connection);
			ResultSet resultSet = metaData.getPrimaryKeys(null, null, tableName);
			List<String> primaryKeys = new ArrayList<String>();
			while(resultSet!=null && resultSet.next()) {
				primaryKeys.add(resultSet.getString(4));
			}
			return primaryKeys;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
}

